package utiles;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

//import java.util.ArrayList;
//import java.util.List;

//import java.util.Hashtable;

import java.util.MissingResourceException;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
//import com.far.dto.ComboDTO;


public class ConexionPool {
	private static Logger log = Logger.getLogger(ConexionPool.class);
	//private Hashtable <Connection,Boolean> connections = new Hashtable<Connection,Boolean>();  

	 public static  Connection getConnectionPool() throws MissingResourceException, IOException {
		 try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		 //String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
		  String url  = AppProperties.getProperty("aplicacion","urlJDBC");
	      String user = AppProperties.getProperty("aplicacion","userDB");
          String pass = AppProperties.getProperty("aplicacion","passDB");
        //  String url1="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.82)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=PMX000)))";
		  Connection conn = null;
		 try {
			 conn = DriverManager.getConnection(url,user,pass);
			 
			/* PoolProperties p = new PoolProperties();
			 p.setUrl(url);
			 p.setDriverClassName("oracle.jdbc.OracleDriver");
			 p.setUsername(user);
			 p.setPassword(pass);
			//p.setJmxEnabled(true);
			//p.setTestWhileIdle(false);
			// p.setTestOnBorrow(true);			
			// p.setTestOnReturn(false);
			// p.setValidationInterval(30000);
			// p.setTimeBetweenEvictionRunsMillis(30000);
			   p.setMaxActive(1); //2	      
			   p.setInitialSize(0);//2
			// p.setMaxWait(10000);//10000
			// p.setMinEvictableIdleTimeMillis(30000);//30000
			   p.setMinIdle(1);
			   p.setMaxIdle(1);
			 //p.setLogAbandoned(true);
			   p.setRemoveAbandoned(true);
			   p.setRemoveAbandonedTimeout(10);//60
			 p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
					 "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
			 DataSource datasource = new DataSource();
			 datasource.setPoolProperties(p); 
			 conn = datasource.getConnection();*/
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			   log.error("Error SQL en  ConexionPool.getConnectionPool!!!: " + e.getMessage());
		       	System.out.println("ConexionDAO.ConexionPool.getConnectionPool :"+e.getMessage());
			}  
		    //System.out.println("conecta:");
	        return conn;
	    }
	 
	 
	    
	    
	    public static void desconectaPool(Connection conn) throws SQLException {
	        try {
	        	
	        	//System.out.println("1 desconecta: ");
	        	//while(conn != null){
	            if(conn != null){	            	
	                conn.close();
	                conn = null;
	                System.out.println("des_conecta:");	               
	           }
	        } catch (SQLException e) {
	            System.out.println("Error SQL: " + e.getMessage());
	            log.error("Error SQL ConexionDAO.getConnection en desconexion!!!: " + e.getMessage());
	        }
	        
	    }
	    
	    public static void main(String[] args) throws SQLException, IOException {
            //public List getAllRegion() throws SQLException, IOException{
			  String url  = "jdbc:oracle:thin:@//softramc2:1521/PMX000";
		      String user = "FLASHDS";
	          String pass = "FLASH";
	    	  PoolProperties p = new PoolProperties();
		 	    p.setUrl(url);
		        p.setDriverClassName("oracle.jdbc.OracleDriver");
		        p.setUsername(user);
		        p.setPassword(pass);
		        p.setJmxEnabled(true);
		        p.setTestWhileIdle(false);
		        p.setTestOnBorrow(true);
		        //p.setValidationQuery("SELECT 1");
		        p.setTestOnReturn(false);
		        p.setValidationInterval(30000);
		        p.setTimeBetweenEvictionRunsMillis(30000);
		        p.setMaxActive(2);	      
		        p.setInitialSize(2);
		        p.setMaxWait(10000);
		        p.setRemoveAbandonedTimeout(10);//60
		        p.setMinEvictableIdleTimeMillis(30000);
		        p.setMinIdle(2);
		        p.setMaxIdle(2);
		        p.setLogAbandoned(true);
		        p.setRemoveAbandoned(true);
		        p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
		          "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
		        DataSource datasource = new DataSource();
		        datasource.setPoolProperties(p); 
		        Connection conn = null;
					conn = datasource.getConnection();                   
                    //List<ComboDTO> listRegion = new ArrayList();
                    try {
                            String query = "{ call FLASHDS.Pkg_rec_combos.getAllRegion(?) }";                
                            CallableStatement  stmt = conn.prepareCall(query);
                            stmt.registerOutParameter(1, OracleTypes.CURSOR);
                            stmt.execute();
                            ResultSet rs = (ResultSet)stmt.getObject(1);

                            while (rs.next()) {
                            	//ComboDTO regionDTO = new ComboDTO();
                                  //  regionDTO.(rs.getString(1));
                                    System.out.println("ID:"+rs.getString(1));
                                    //regionDTO.setDescripcion(rs.getString(2));        
                                    System.out.println("Desc:"+rs.getString(2));
                                    //listRegion.add(regionDTO);
                            }
                            rs.close();
                            stmt.close();
                    } catch (SQLException ex) {  
                            log.error("Error ComboDAO.getAllRegion SQLException: " +ex.getMessage());
                            System.out.println(ex.getMessage());
                    } finally {
                            conn.close();
                            conn = null;
                    }
                    //return listRegion;

            }
}
